Databases are used for storing large amounts of data. Online Transaction Processing (OLTP) systems are configured for frequent update of records quickly and efficiently, but have limited capacity for performing aggregated and complex queries. In contrast, data warehouses (DWH) are optimized for searching large amounts of information, but are designed for less frequent update.
In order to prevent errors, many databases are locked for access by other users before they are updated and the lock is released only when the update is completed. If a data warehouse requires frequent updates, the locking of the data warehouse may cause substantial interruptions in the service provided by the warehouse.
Accordingly, there have been suggested various systems which combine an OLTP system with a data warehouse.
US patent publication 2005/0289129 to Schmitt describes an OLTP database and OLAP database which are synchronized periodically during the night.
US patent publication 2009/0319546 to Shaik describes methods for transferring data from an OLTP system to a data warehouse.
US patent application publication 2005/0065944 to Gunther et al. describes a system in which data is transferred from an OLTP database to a warehouse database. Timestamps are used to indicate the data which is to be transferred in periodic data warehouse updates.
Other systems for updating databases are also known, as well as methods for avoiding the need for frequent updates.
U.S. Pat. No. 7,882,087 to Johnson describes a method of updating a data storage system. A database includes raw tables and derived tables and a system for updating the derived table with the data of the raw tables.
US patent application publication 2011/0010330 describes managing an append-only table and a separate update table within a data warehouse, so as to avoid the need to update the data warehouse frequently.
US patent application publication 2009/0187582 to Chen et al., describes a method of frequent updating of a large warehouse.